package com.ruoyi.config.sharding;

import com.baomidou.mybatisplus.annotation.TableField;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.sql.ResultSetMetaData;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库分表工具类，支持按天、按月和按年分表
 */
@Component
public class ShardingTableUtils {
    private static final Logger logger = LoggerFactory.getLogger(ShardingTableUtils.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 分表类型枚举
     */
    public enum ShardingType {
        DAILY,  // 按天
        MONTHLY, // 按月
        YEARLY  // 按年
    }

    /**
     * 根据日期获取分表名
     *
     * @param baseTableName 基础表名
     * @param date          日期
     * @param shardingType  分表类型
     * @return 分表名
     */
    public String getShardingTableName(String baseTableName, LocalDate date, ShardingType shardingType) {
        String formatPattern = "";
        switch (shardingType) {
            case DAILY:
                formatPattern = "yyyyMMdd";
                break;
            case MONTHLY:
                formatPattern = "yyyyMM";
                break;
            case YEARLY:
                formatPattern = "yyyy";
                break;
            default:
                throw new IllegalArgumentException("不支持的分表类型: " + shardingType);
        }

        String dateSuffix = date.format(DateTimeFormatter.ofPattern(formatPattern));
        return baseTableName + "_" + dateSuffix;
    }

    /**
     * 获取指定日期范围内的所有分表名
     *
     * @param baseTableName 基础表名
     * @param startDate     开始日期
     * @param endDate       结束日期
     * @param shardingType  分表类型
     * @return 分表名列表
     */
    public List<String> getShardingTableNamesInRange(String baseTableName, LocalDate startDate, LocalDate endDate, ShardingType shardingType) {
        List<String> tableNames = new ArrayList<>();

        LocalDate current = startDate;
        while (!current.isAfter(endDate)) {
            tableNames.add(getShardingTableName(baseTableName, current, shardingType));

            // 根据分表类型增加日期
            switch (shardingType) {
                case DAILY:
                    current = current.plusDays(1);
                    break;
                case MONTHLY:
                    current = current.plusMonths(1);
                    break;
                case YEARLY:
                    current = current.plusYears(1);
                    break;
            }
        }

        return tableNames;
    }

    /**
     * 检查表是否存在
     *
     * @param tableName 表名
     * @return 是否存在
     */
    public boolean tableExists(String tableName) {
        String sql = "SHOW TABLES LIKE ?";
        return jdbcTemplate.query(sql, new Object[]{tableName}, (rs, rowNum) -> rs.getString(1))
                .stream().findFirst().isPresent();
    }

    /**
     * 根据基础表创建分表
     *
     * @param baseTableName 基础表名
     * @param newTableName  新表名
     */
    public void createTableFromBase(String baseTableName, String newTableName) {
        if (tableExists(newTableName)) {
            logger.info("表 {} 已存在，无需创建", newTableName);
            return;
        }

        String createTableSql = "CREATE TABLE `" + newTableName + "` LIKE `" + baseTableName + "`";
        jdbcTemplate.execute(createTableSql);
        logger.info("成功创建表 {}", newTableName);
    }

    /**
     * 创建指定日期的分表
     *
     * @param baseTableName 基础表名
     * @param date          日期
     * @param shardingType  分表类型
     */
    public void createTableByDate(String baseTableName, LocalDate date, ShardingType shardingType) {
        String tableName = getShardingTableName(baseTableName, date, shardingType);
        createTableFromBase(baseTableName, tableName);
    }

    /**
     * 获取表的创建语句
     *
     * @param tableName 表名
     * @return 创建语句
     */
    public String getTableCreateStatement(String tableName) {
        String sql = "SHOW CREATE TABLE `" + tableName + "`";
        return jdbcTemplate.query(sql, (rs, rowNum) -> rs.getString(2)).get(0);
    }

    /**
     * 删除表
     *
     * @param tableName 表名
     */
    public void dropTable(String tableName) {
        if (!tableExists(tableName)) {
            logger.info("表 {} 不存在，无需删除", tableName);
            return;
        }

        String sql = "DROP TABLE " + tableName;
        jdbcTemplate.execute(sql);
        logger.info("成功删除表 {}", tableName);
    }

    /**
     * 获取表的所有列名
     *
     * @param tableName 表名
     * @return 列名列表
     */
    public List<String> getTableColumns(String tableName) {
        List<String> columns = new ArrayList<>();
        String sql = "SHOW COLUMNS FROM " + tableName;

        jdbcTemplate.query(sql, (rs, rowNum) -> {
            columns.add(rs.getString("Field"));
            return null;
        });

        return columns;
    }

    /**
     * 将对象属性映射为SQL插入语句
     *
     * @param object    对象
     * @param tableName 表名
     * @return 插入语句和参数
     */
    public Map<String, Object> buildInsertSql(Object object, String tableName) {
        Map<String, Object> result = new HashMap<>();

        StringBuilder columns = new StringBuilder();
        StringBuilder values = new StringBuilder();
        List<Object> params = new ArrayList<>();

        Class<?> clazz = object.getClass();
        Field[] fields = clazz.getDeclaredFields();

        boolean isFirst = true;
        for (Field field : fields) {
            field.setAccessible(true);
            try {
                Object value = field.get(object);
                if (value != null) {
                    if (!isFirst) {
                        columns.append(", ");
                        values.append(", ");
                    }

                    // 获取列名（优先使用 @TableField 注解的值，否则使用属性名）
                    String columnName = field.getName();
                    TableField tableField = field.getAnnotation(TableField.class);
                    if (tableField != null && !tableField.value().isEmpty()) {
                        columnName = tableField.value();
                    }

                    columns.append("`").append(columnName).append("`");
                    values.append("?");
                    params.add(value);

                    isFirst = false;
                }
            } catch (IllegalAccessException e) {
                logger.error("获取对象属性值失败", e);
            }
        }

        String sql = "INSERT INTO `" + tableName + "` (" + columns + ") VALUES (" + values + ")";
        result.put("sql", sql);
        result.put("params", params);

        return result;
    }

    /**
     * 执行插入操作
     *
     * @param object    对象
     * @param tableName 表名
     * @return 插入结果
     */
    public int executeInsert(Object object, String tableName) {
        Map<String, Object> insertInfo = buildInsertSql(object, tableName);
        String sql = (String) insertInfo.get("sql");
        List<Object> params = (List<Object>) insertInfo.get("params");
        return jdbcTemplate.update(sql, params.toArray());
    }

    /**
     * 根据日期查询最近的N条记录
     *
     * @param baseTableName    基础表名
     * @param limit            查询数量
     * @param shardingType     分表类型
     * @param maxTablesToCheck 最多检查的表数量
     * @param startDate        开始日期
     * @return 最近的记录列表
     */
    public List<Map<String, Object>> queryLatestRecordsByDate(String baseTableName, int limit,
                                                              ShardingType shardingType,
                                                              int maxTablesToCheck,
                                                              LocalDate startDate,
                                                              String sensorId) {
        // 存储结果的列表
        List<Map<String, Object>> result = new ArrayList<>();

        // 检查表的数量
        int tablesChecked = 0;

        // 从开始日期开始，逐个检查表
        LocalDate dateToCheck = startDate;
        while (tablesChecked < maxTablesToCheck) {
            // 获取当前日期对应的表名
            String tableName = getShardingTableName(baseTableName, dateToCheck, shardingType);
            // 检查表是否存在
            if (tableExists(tableName)) {
                // 查询该表中的记录
                String sql = "SELECT * FROM `" + tableName + "` " +
                        "WHERE sensor_id = ? and collect_time <= ? " +  // 只查询小于等于指定日期的记录
                        "ORDER BY collect_time DESC LIMIT ?";
                // 执行查询
                List<Map<String, Object>> records = jdbcTemplate.query(sql,
                        new Object[]{sensorId,dateToCheck.plusDays(1).atStartOfDay(), limit - result.size()},
                        (rs, rowNum) -> {
                            Map<String, Object> record = new HashMap<>();
                            ResultSetMetaData metaData = rs.getMetaData();
                            int columnCount = metaData.getColumnCount();
                            for (int i = 1; i <= columnCount; i++) {
                                record.put(metaData.getColumnName(i), rs.getObject(i));
                            }
                            return record;
                        });

                // 将结果添加到总结果列表中
                result.addAll(records);

                // 如果已经获取到足够的记录，就退出循环
                if (result.size() >= limit) {
                    result = result.subList(0, limit);
                    break;
                }
            }

            // 移动到前一个时间单位
            switch (shardingType) {
                case DAILY:
                    dateToCheck = dateToCheck.minusDays(1);
                    break;
                case MONTHLY:
                    dateToCheck = dateToCheck.minusMonths(1);
                    break;
                case YEARLY:
                    dateToCheck = dateToCheck.minusYears(1);
                    break;
            }

            tablesChecked++;
        }

        // 按时间排序（确保结果是全局有序的）
        result.sort((r1, r2) -> {
            LocalDateTime time1 = (LocalDateTime) r1.get("collect_time");
            LocalDateTime time2 = (LocalDateTime) r2.get("collect_time");
            return time2.compareTo(time1); // 降序排列
        });

        // 如果结果超过limit，截取前limit条
        if (result.size() > limit) {
            result = result.subList(0, limit);
        }

        return result;
    }
}